package com.hhit.bigscreenbackmanage.util;

import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.cglib.beans.BeanMap;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author Administrator
 * @DATA 2020-09-01
 * */
public class Utils {

    /**
     * 对象集合转集合Map
     * @author
     * @param beanList  对象集合
     * @return List<Map<String,Object>>
     */
    public static List<Map> entityListToMap(List<?> beanList) {
        List<Map> resultList = new ArrayList<>();
        for (int i = 0; i < beanList.size(); i++) {
            Map mapConfig = new HashMap();
            BeanMap beanMap = BeanMap.create(beanList.get(i));
            for ( Object  key : beanMap.keySet()){
                mapConfig.put(key+"",beanMap.get(key));
            }
            resultList.add(mapConfig);
        }
        return resultList;
    }

    /**
     * 将List<Map<String,Object>>转换为List<T>
     *
     * @param maps
     * @param clazz
     * @return
     * @throws InstantiationException
     * @throws IllegalAccessException
     */
    public static <T> List<T> mapToEntiry(List<Map<String, Object>> maps, Class<T> clazz)
            throws InstantiationException, IllegalAccessException {
        List<T> list = Lists.newArrayList();
        if (maps != null && maps.size() > 0) {
            Map<String, Object> map = null;
            T bean = null;
            for (int i = 0, size = maps.size(); i < size; i++) {
                map = maps.get(i);
                bean = clazz.newInstance();
                mapToBean(map, bean);
                list.add(bean);
            }
        }
        return list;
    }
    public static <T> T mapToBean(Map<String, Object> map, T bean) {
        BeanMap beanMap = BeanMap.create(bean);
        beanMap.putAll(map);
        return bean;
    }

    /**
     * 导出Excel
     * @param tableName      导出的表名
     * @param resultList     数据集合
     * @param colNameList    excel列名集合
     * @param keyValueList   excel列值集合
     * @param request
     * @param response
     */
    public static void exportExcel(String tableName, List<Map> resultList, List<String> colNameList, List<String> keyValueList, HttpServletRequest request, HttpServletResponse response) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            Map<String, Short> allRowHeight = new HashMap<String, Short>();
            allRowHeight.put("titleHeight", (short) 800);
            allRowHeight.put("totalHeight", (short) 650);
            allRowHeight.put("colHeight", (short) 600);
            allRowHeight.put("rowHeight", (short) 500);
            //列名
            Map<String, Integer> colNamesWidth = new LinkedHashMap<String, Integer>();
            for (int i = 0; i < colNameList.size(); i++) {
                colNamesWidth.put(colNameList.get(i),20);
            }
            //设值
            Map<String, String> colKeysStyle = new LinkedHashMap<String, String>();
            for (int i = 0; i < keyValueList.size(); i++) {
                colKeysStyle.put(keyValueList.get(i),"center");
            }
            ExcelUtils.exportExcel3(workbook, 0, tableName, true,
                    allRowHeight, colNamesWidth, colKeysStyle, resultList, request, response);
            ExcelUtils.closeOuputStream(response,workbook,tableName);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取properties列名列值
     * @author
     * @param
     * @return
     */
    public static List<String> columnName(String nameAndValue) {
        List<String> resultList = new ArrayList<>();
        Properties p = PropertiesUtils.getPropertys();
        if(nameAndValue.endsWith("Name")){
            String[] result = p.getProperty(nameAndValue).split(",");
            for (String s:result) {
                resultList.add(s);
            }
        }else if(nameAndValue.endsWith("Value")){
            String[] result = p.getProperty(nameAndValue).split(",");
            for (String s:result) {
                resultList.add(s);
            }
        }
        return resultList;
    }

    /**
     *  获取Excel数据内容
     * @param propName  properties中列字段名
     * @param files
     * @return  List
     */
    public static List<Map<String,Object>> getExcelData(String propName, MultipartFile[] files) {

        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        List<Map<String,Object>> resultList = new ArrayList<>();
        InputStream is = null;
        try {
            for (MultipartFile file : files) {
                String fileName = file.getOriginalFilename();
                if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                    System.out.println("文件格式不正确");
                }else{
                    boolean isExcel2003 = true;
                    if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                        isExcel2003 = false;
                    }
                    Sheet sheet = null;
                    if (isExcel2003) {
                        is = file.getInputStream();
                        HSSFWorkbook wb = new HSSFWorkbook(is);
                        sheet = wb.getSheetAt(0);
                    }else {
                        is = file.getInputStream();
                        XSSFWorkbook wb = new XSSFWorkbook(is);
                        sheet = wb.getSheetAt(0);
                    }

                    //校验文件是否为空
                    if(sheet.getLastRowNum()>0) {
                        List<String> keyValueList = columnName(propName);//列值
                        if(keyValueList.size() > 0 ){
                            int rowNumber=0;
                            //外层循环判读数据读取的起始位置
                            for(int r =0; r <=sheet.getLastRowNum(); r++){
                                Row row = sheet.getRow(r);
                                rowNumber++;
                                String cellValue="";
                                //将单元格每一行的第一个单元格的数据类型设置成String
                                if(row!=null && row.getCell(0)!=null){
                                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                                    //获取每一行的第一个单元格的数据
                                    cellValue = row.getCell(0).getStringCellValue();
                                }
                                //判断序号为1的时候然后开始读取剩余行的数据
                                if(cellValue.equals("1") || cellValue.equals("1.0") ){
                                    int targetRowNumber = rowNumber-1;
                                    //中间循环 读取行
                                    for (int r1 =targetRowNumber; r1 <=sheet.getLastRowNum(); targetRowNumber++) {
                                        Row row1 = sheet.getRow(targetRowNumber);
                                        if(row1 == null){
                                            break;
                                        }
                                        if(row1!=null){
                                            if (row1.getCell(0)==null) {
                                                System.out.println(row1);
                                            } else {
                                                row1.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                                            }
                                        }
                                        //只录取每行第一个单元格有序号的数据
                                        if (row1.getCell(0)!=null) {
                                            if(!"".equals((row1.getCell(0).getStringCellValue()))){
                                                Map<String,Object> rmap = new HashMap<>();
                                                //内层循环 读取列
                                                for (int k = 0; k <keyValueList.size() ; k++) {
                                                    if( row1.getCell(k)!=null){
                                                        Cell cell = row1.getCell(k);
                                                        int cellType = cell.getCellType();
                                                        String targetCellValue = null;
                                                        switch(cellType) {
                                                            case Cell.CELL_TYPE_STRING: //文本
                                                                targetCellValue = cell.getStringCellValue();
                                                                break;
                                                            case Cell.CELL_TYPE_NUMERIC: //数字、日期
                                                                if(DateUtilRewrite.isCellDateFormatted(cell)) {
                                                                    targetCellValue = fmt.format(cell.getDateCellValue()); //日期型
                                                                }
                                                                else {
                                                                    //数字
                                                                    String formatType = cell.getCellStyle().getDataFormatString()==null?"":cell.getCellStyle().getDataFormatString();
                                                                    if(formatType.indexOf("%") != -1){
                                                                        double numericCellValue = cell.getNumericCellValue();
                                                                        boolean fuShu = false;
                                                                        if(numericCellValue < 0){
                                                                            fuShu = true;
                                                                            numericCellValue = -numericCellValue;
                                                                        }
                                                                        int baifenshuxiaoshuwei = 0;
                                                                        if(formatType.indexOf(".")!=-1){
                                                                            baifenshuxiaoshuwei = formatType.substring(formatType.indexOf("."),formatType.indexOf("%")).length()-1; // 获取小数位数
                                                                            targetCellValue = "#.";
                                                                            for(int weishu=0;weishu<baifenshuxiaoshuwei;weishu++) {
                                                                                targetCellValue += "0";
                                                                            }
                                                                            targetCellValue = new DecimalFormat(targetCellValue).format(numericCellValue*100) + "%";
                                                                            if(targetCellValue.startsWith(".")) {// 如果以.开头的，前面加0拼接；
                                                                                targetCellValue = "0" + targetCellValue;
                                                                            }
                                                                        }else {
                                                                            targetCellValue = Math.round(numericCellValue*100)+"%";
                                                                        }
                                                                        if(fuShu){
                                                                            targetCellValue = "-"+targetCellValue;
                                                                        }
                                                                    }else {
                                                                        cell.setCellType(Cell.CELL_TYPE_STRING);
                                                                        targetCellValue = cell.getStringCellValue();
                                                                    }
                                                                }
                                                                break;
                                                            case Cell.CELL_TYPE_BOOLEAN: //布尔型
                                                                targetCellValue = String.valueOf(cell.getBooleanCellValue());
                                                                break;
                                                            case Cell.CELL_TYPE_BLANK: //空白
                                                                targetCellValue = cell.getStringCellValue();
                                                                break;
                                                            case Cell.CELL_TYPE_ERROR: //错误
                                                                targetCellValue = "错误";
                                                                break;
                                                            case Cell.CELL_TYPE_FORMULA: //公式
                                                                //尾工管理那个函数日期用
                                                                //targetCellValue = fmt.format(cell.getDateCellValue());
                                                                //targetCellValue = "错误";
                                                                try {

                                                                    String formatType = cell.getCellStyle().getDataFormatString();
                                                                    if(formatType.indexOf("%") != -1){
                                                                        double numericCellValue = cell.getNumericCellValue();
                                                                        boolean fuShu = false;
                                                                        if(numericCellValue < 0){
                                                                            fuShu = true;
                                                                            numericCellValue = -numericCellValue;
                                                                        }
                                                                        int baifenshuxiaoshuwei = 0;
                                                                        if(formatType.indexOf(".")!=-1){
                                                                            baifenshuxiaoshuwei = formatType.substring(formatType.indexOf("."),formatType.indexOf("%")).length()-1; // 获取小数位数
                                                                            targetCellValue = "#.";
                                                                            for(int weishu=0;weishu<baifenshuxiaoshuwei;weishu++) {
                                                                                targetCellValue += "0";
                                                                            }
                                                                            targetCellValue = new DecimalFormat(targetCellValue).format(numericCellValue*100) + "%";
                                                                            if(targetCellValue.startsWith(".")) {// 如果以.开头的，前面加0拼接；
                                                                                targetCellValue = "0" + targetCellValue;
                                                                            }
                                                                        }else {
                                                                            targetCellValue = Math.round(numericCellValue*100)+"%";
                                                                        }
                                                                        if(fuShu){
                                                                            targetCellValue = "-"+targetCellValue;
                                                                        }
                                                                    }else {
                                                                        targetCellValue = String.valueOf(cell.getNumericCellValue());
                                                                    }
                                                                } catch (IllegalStateException e) {
                                                                    targetCellValue = String.valueOf(cell.getRichStringCellValue());
                                                                }
                                                                break;
                                                            default:
                                                                targetCellValue = "错误";
                                                        }
                                                        //存储每个单元格数据
                                                        rmap.put(keyValueList.get(k),targetCellValue);
                                                    }
                                                }
                                                resultList.add(rmap);
                                            }
                                        }

                                    }
                                }
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("解析excel异常"+e.getMessage());
        }finally{
            if(is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return resultList;
    }

    /**
     *  校验时间格式
     * @param dateTime     时间       例如：2020-09-05 09:09:09
     * @param formatTime   时间格式   例如：yyyy-MM-dd HH:mm:ss
     * @return
     */
    public static boolean timeFormat(String dateTime,String formatTime){
        boolean flag =  true;
        try {
            SimpleDateFormat format =  new SimpleDateFormat(formatTime);
            format.setLenient(false);
            format.parse(dateTime);
        } catch (Exception e) {
            e.printStackTrace();
            flag = false;
        }
        return flag ;
    }

    /**
     * 把长报文拆分成多个子报文
     * 拆分规则：以begin开始，以end结尾
     * @param info 待拆分的长报文
     * @param begin 开始字符
     * @param end 结尾字符
     * @return 符合规则的子报文集合
     */
    public static List<String> getChildMsgByParent(String info,String begin,String end){
        //通过起始字符拆分成数组
        String[] split = info.split(begin);
        List<String> list = new ArrayList<>();
        //遍历，从第二个元素开始取值（第一个元素为无效元素）
        for (int i = 1; i < split.length; i++) {
            String str = split[i].substring(0,split[i].lastIndexOf(end)+end.length());
            if (str.length() > end.length()) {
                list.add(begin+str);
            }
        }
        return list;
    }


    /**
     *  获取Excel数据内容
     * @param propName  properties中列字段名
     * @return  List
     */
    public static List<Map<String,Object>> getExcelDataForGis(String propName, MultipartFile[] files) {

        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        List<Map<String,Object>> resultList = new ArrayList<>();
        InputStream is = null;
        try {
            for (MultipartFile file : files) {
                String fileName = file.getOriginalFilename();
                if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
                    System.out.println("文件格式不正确");
                }else{
                    boolean isExcel2003 = true;
                    if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
                        isExcel2003 = false;
                    }
                    Sheet sheet = null;
                    if (isExcel2003) {
                        is = file.getInputStream();
                        HSSFWorkbook wb = new HSSFWorkbook(is);
                        sheet = wb.getSheetAt(0);
                    }else {
                        is = file.getInputStream();
                        XSSFWorkbook wb = new XSSFWorkbook(is);
                        sheet = wb.getSheetAt(0);
                    }

                    //校验文件是否为空
                    if(sheet.getLastRowNum()>0) {
                        List<String> keyValueList = columnName(propName);//列值
                        if(keyValueList.size() > 0 ){
                            int rowNumber=0;
                            //外层循环判读数据读取的起始位置
                            String pjName = "";
                            for(int r =0; r <=sheet.getLastRowNum(); r++){
                                Row row = sheet.getRow(r);
                                rowNumber++;
                                String cellValue="";
                                //将单元格每一行的第一个单元格的数据类型设置成String
                                if(row!=null && row.getCell(0)!=null){
                                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                                    //获取每一行的第一个单元格的数据
                                    cellValue = row.getCell(0).getStringCellValue();
                                }
                                if (cellValue.indexOf("工程名称")!=-1) {
                                    pjName = row.getCell(1).getStringCellValue();
                                }

                                //判断序号为1的时候然后开始读取剩余行的数据
                                if(cellValue.equals("1") || cellValue.equals("1.0") ){
                                    int targetRowNumber = rowNumber-1;
                                    //中间循环 读取行
                                    for (int r1 =targetRowNumber; r1 <=sheet.getLastRowNum(); targetRowNumber++) {
                                        Row row1 = sheet.getRow(targetRowNumber);
                                        if(row1 == null){
                                            break;
                                        }
                                        if(row1!=null){
                                            if (row1.getCell(0)!=null) {
                                                row1.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
                                            }
                                        }
                                        //只录取每行第一个单元格有序号的数据
                                        if(!"".equals((row1.getCell(0).getStringCellValue()))){
                                            Map<String,Object> rmap = new HashMap<>();
                                            //内层循环 读取列
                                            for (int k = 0; k <keyValueList.size() ; k++) {
                                                if( row1.getCell(k)!=null){
                                                    Cell cell = row1.getCell(k);
                                                    int cellType = cell.getCellType();
                                                    String targetCellValue = null;
                                                    switch(cellType) {
                                                        case Cell.CELL_TYPE_STRING: //文本
                                                            targetCellValue = cell.getStringCellValue();
                                                            break;
                                                        case Cell.CELL_TYPE_NUMERIC: //数字、日期
                                                            if(DateUtilRewrite.isCellDateFormatted(cell)) {
                                                                targetCellValue = fmt.format(cell.getDateCellValue()); //日期型
                                                            }
                                                            else {
                                                                //数字
                                                                String formatType = cell.getCellStyle().getDataFormatString();
                                                                if(formatType.indexOf("%") != -1){
                                                                    double numericCellValue = cell.getNumericCellValue();
                                                                    boolean fuShu = false;
                                                                    if(numericCellValue < 0){
                                                                        fuShu = true;
                                                                        numericCellValue = -numericCellValue;
                                                                    }
                                                                    int baifenshuxiaoshuwei = 0;
                                                                    if(formatType.indexOf(".")!=-1){
                                                                        baifenshuxiaoshuwei = formatType.substring(formatType.indexOf("."),formatType.indexOf("%")).length()-1; // 获取小数位数
                                                                        targetCellValue = "#.";
                                                                        for(int weishu=0;weishu<baifenshuxiaoshuwei;weishu++) {
                                                                            targetCellValue += "0";
                                                                        }
                                                                        targetCellValue = new DecimalFormat(targetCellValue).format(numericCellValue*100) + "%";
                                                                        if(targetCellValue.startsWith(".")) {// 如果以.开头的，前面加0拼接；
                                                                            targetCellValue = "0" + targetCellValue;
                                                                        }
                                                                    }else {
                                                                        targetCellValue = Math.round(numericCellValue*100)+"%";
                                                                    }
                                                                    if(fuShu){
                                                                        targetCellValue = "-"+targetCellValue;
                                                                    }
                                                                }else {
                                                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                                                    targetCellValue = cell.getStringCellValue();
                                                                }
                                                            }
                                                            break;
                                                        case Cell.CELL_TYPE_BOOLEAN: //布尔型
                                                            targetCellValue = String.valueOf(cell.getBooleanCellValue());
                                                            break;
                                                        case Cell.CELL_TYPE_BLANK: //空白
                                                            targetCellValue = cell.getStringCellValue();
                                                            break;
                                                        case Cell.CELL_TYPE_ERROR: //错误
                                                            targetCellValue = "错误";
                                                            break;
                                                        case Cell.CELL_TYPE_FORMULA: //公式
                                                            //尾工管理那个函数日期用
                                                            //targetCellValue = fmt.format(cell.getDateCellValue());
                                                            //targetCellValue = "错误";
                                                            try {

                                                                String formatType = cell.getCellStyle().getDataFormatString();
                                                                if(formatType.indexOf("%") != -1){
                                                                    double numericCellValue = cell.getNumericCellValue();
                                                                    boolean fuShu = false;
                                                                    if(numericCellValue < 0){
                                                                        fuShu = true;
                                                                        numericCellValue = -numericCellValue;
                                                                    }
                                                                    int baifenshuxiaoshuwei = 0;
                                                                    if(formatType.indexOf(".")!=-1){
                                                                        baifenshuxiaoshuwei = formatType.substring(formatType.indexOf("."),formatType.indexOf("%")).length()-1; // 获取小数位数
                                                                        targetCellValue = "#.";
                                                                        for(int weishu=0;weishu<baifenshuxiaoshuwei;weishu++) {
                                                                            targetCellValue += "0";
                                                                        }
                                                                        targetCellValue = new DecimalFormat(targetCellValue).format(numericCellValue*100) + "%";
                                                                        if(targetCellValue.startsWith(".")) {// 如果以.开头的，前面加0拼接；
                                                                            targetCellValue = "0" + targetCellValue;
                                                                        }
                                                                    }else {
                                                                        targetCellValue = Math.round(numericCellValue*100)+"%";
                                                                    }
                                                                    if(fuShu){
                                                                        targetCellValue = "-"+targetCellValue;
                                                                    }
                                                                }else {
                                                                    targetCellValue = String.valueOf(cell.getNumericCellValue());
                                                                }
                                                            } catch (IllegalStateException e) {
                                                                targetCellValue = String.valueOf(cell.getRichStringCellValue());
                                                            }
                                                            break;
                                                        default:
                                                            targetCellValue = "错误";
                                                    }
                                                    //存储每个单元格数据
                                                    rmap.put(keyValueList.get(k),targetCellValue);
                                                }
                                            }
                                            rmap.put("projectName", pjName);
                                            resultList.add(rmap);
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("解析excel异常"+e.getMessage());
        }finally{
            if(is != null){
                try {
                    is.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return resultList;
    }

}
